﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using RTGS.Library;
using RTGS.Library.Entities.UserManagement;
using RTGS.Library.Enum;
using RTGS.Library.Interfaces;

namespace RTGS.Library.DataAccess.UserManagement
{
    

    public partial class sysTRoleRepo : ISearchable<sysTRole>
    {
        //public List<sysTRole> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        //{
        //    List<sysTRole> tt = new List<sysTRole>();
        //    using (SqlConnection conn = new SqlConnection(connectionString))
        //    {
        //        using (SqlCommand cmd = conn.CreateCommand())
        //        {
        //            int startRow = ((currentPage - 1) * pageSize);
        //            int endRow = (startRow + pageSize);

        //            StringBuilder sbQuery = new StringBuilder();
        //            sbQuery.AppendFormat(" SELECT * ");
        //            sbQuery.AppendFormat(" FROM (SELECT TOP({0}) ", pageSize);
        //            sbQuery.AppendFormat("          r.* ");
        //            sbQuery.AppendFormat("       FROM (SELECT TOP({0} + {1} - 1) Role_ID, Role_Name, Created_By, Created_Date, Modified_By, Modified_Date ", startRow, pageSize);
        //            sbQuery.AppendFormat("              FROM sys_t_role ");
        //            sbQuery.AppendFormat("              {0}", parameters);
        //            sbQuery.AppendFormat("              ORDER BY {0} {1}) r ", sortName, sortOrder);
        //            sbQuery.AppendFormat("       ORDER BY {0} DESC) r ", sortName);
        //            sbQuery.AppendFormat(" ORDER BY {0}", sortName);

        //            cmd.CommandText = sbQuery.ToString();
        //            cmd.CommandType = CommandType.Text;
        //            try
        //            {
        //                conn.Open();
        //                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        //                {
        //                    while (dr.Read())
        //                    {
        //                        sysTRole t = new sysTRole();
        //                        t.roleId = dr[sysTRole.Fields.roleId] == DBNull.Value ? 0 : int.Parse(dr[sysTRole.Fields.roleId].ToString());
        //                        t.roleName = dr[sysTRole.Fields.roleName] == DBNull.Value ? string.Empty : dr[sysTRole.Fields.roleName].ToString();
        //                        t.createdBy = dr[sysTRole.Fields.createdBy] == DBNull.Value ? string.Empty : dr[sysTRole.Fields.createdBy].ToString();
        //                        t.createdDate = dr[sysTRole.Fields.createdDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[sysTRole.Fields.createdDate].ToString());
        //                        t.modifiedBy = dr[sysTRole.Fields.modifiedBy] == DBNull.Value ? string.Empty : dr[sysTRole.Fields.modifiedBy].ToString();
        //                        t.modifiedDate = dr[sysTRole.Fields.modifiedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[sysTRole.Fields.modifiedDate].ToString());
        //                        tt.Add(t);
        //                        t.Dispose();
        //                    }
        //                }
        //            }
        //            catch (Exception ex)
        //            {
        //                errorLog = ex.Message;
        //            }
        //            finally
        //            {
        //                if (conn.State == ConnectionState.Open) conn.Close();
        //            }
        //        }
        //    }
        //    return tt;
        //}

        //public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        //{
        //    object _result = null;
        //    using (SqlConnection conn = new SqlConnection(connectionString))
        //    {
        //        using (SqlCommand cmd = conn.CreateCommand())
        //        {
        //            StringBuilder sbQuery = new StringBuilder();
        //            switch (function)
        //            {
        //                case SQL.Function.Aggregate.Max:
        //                    sbQuery.AppendFormat("SELECT MAX({0}) FROM sys_t_role {1}", column, parameters);
        //                    break;
        //                case SQL.Function.Aggregate.Min:
        //                    sbQuery.AppendFormat("SELECT MIN({0}) FROM sys_t_role {1}", column, parameters);
        //                    break;
        //                case SQL.Function.Aggregate.Distinct:
        //                    sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM sys_t_role {1}", column, parameters);
        //                    break;
        //                case SQL.Function.Aggregate.Count:
        //                    sbQuery.AppendFormat("SELECT COUNT({0}) FROM sys_t_role {1}", column, parameters);
        //                    break;
        //                case SQL.Function.Aggregate.Sum:
        //                    sbQuery.AppendFormat("SELECT SUM({0}) FROM sys_t_role {1}", column, parameters);
        //                    break;
        //                case SQL.Function.Aggregate.Avg:
        //                    sbQuery.AppendFormat("SELECT AVG({0}) FROM sys_t_role {1}", column, parameters);
        //                    break;
        //                default:
        //                    // do nothing 
        //                    break;
        //            }

        //            cmd.CommandText = sbQuery.ToString();
        //            cmd.CommandType = CommandType.Text;
        //            try
        //            {
        //                conn.Open();
        //                _result = cmd.ExecuteScalar();
        //            }
        //            catch (Exception ex)
        //            {
        //                errorLog = ex.Message;
        //            }
        //            finally
        //            {
        //                if (conn.State == ConnectionState.Open) conn.Close();
        //            }
        //        }
        //    }
        //    return _result;
        //}
    }

    public partial class sysTMenuRepo : ISearchable<sysTMenu>
    {
        public List<sysTMenu> GetHierarchyList(string parameters)
        {
            List<sysTMenu> tt = new List<sysTMenu>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string hierarchyData = @"
                            WITH HierarchyData (Menu_ID, Menu_Name, Url, Order_Seq, Level, Parent_ID, Paths)
                            AS
                            (
                                select Menu_ID, Menu_Name, Url, Order_Seq, 0 Level, Parent_ID, cast(menu_id as varchar(50)) Paths
                                from sys_t_menu
                                where Parent_ID IS NULL
                                UNION ALL
                                select 
		                            m.Menu_ID, 
		                            m.menu_Name,
		                            m.Url, m.Order_Seq, Level + 1, m.Parent_ID, 
		                            cast(cast(m.Parent_ID as varchar(50)) + '.' + cast(m.Menu_ID as varchar(50)) as varchar(50)) as Paths
                                from sys_t_menu as m JOIN HierarchyData AS d ON (m.Parent_ID = d.Menu_ID)
                            )
	                            select
		                            m.Menu_ID,
		                            REPLICATE ('     ' , m.Level) + m.Menu_Name AS Menu_Name,
		                            m.Url,
		                            m.Order_Seq,
		                            m.[Level],
		                            m.Parent_ID,
		                            p.Menu_Name as Parent_Name,
		                            Paths
	                            from HierarchyData m LEFT JOIN sys_t_menu p ON (m.Parent_ID = p.Menu_ID) " + parameters + " order by paths";

                    cmd.CommandText = hierarchyData;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                sysTMenu t = new sysTMenu();
                                t.menuId = dr[sysTMenu.Fields.menuId] == DBNull.Value ? 0 : int.Parse(dr[sysTMenu.Fields.menuId].ToString());
                                t.menuName = dr[sysTMenu.Fields.menuName] == DBNull.Value ? string.Empty : dr[sysTMenu.Fields.menuName].ToString();
                                t.url = dr[sysTMenu.Fields.url] == DBNull.Value ? string.Empty : dr[sysTMenu.Fields.url].ToString();
                                t.parentId = dr[sysTMenu.Fields.parentId] == DBNull.Value ? (int?)null : int.Parse(dr[sysTMenu.Fields.parentId].ToString());
                                t.orderSeq = dr[sysTMenu.Fields.orderSeq] == DBNull.Value ? (short?)null : short.Parse(dr[sysTMenu.Fields.orderSeq].ToString());

                                t.level = dr[sysTMenu.Fields.level] == DBNull.Value ? 0 : int.Parse(dr[sysTMenu.Fields.level].ToString());
                                t.parentName = dr[sysTMenu.Fields.parentName] == DBNull.Value ? string.Empty : dr[sysTMenu.Fields.parentName].ToString();
                                
                                //t.createdBy = dr[sysTMenu.Fields.createdBy] == DBNull.Value ? string.Empty : dr[sysTMenu.Fields.createdBy].ToString();
                                //t.createdDate = dr[sysTMenu.Fields.createdDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[sysTMenu.Fields.createdDate].ToString());
                                //t.modifiedBy = dr[sysTMenu.Fields.modifiedBy] == DBNull.Value ? string.Empty : dr[sysTMenu.Fields.modifiedBy].ToString();
                                //t.modifiedDate = dr[sysTMenu.Fields.modifiedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[sysTMenu.Fields.modifiedDate].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        errorLog = ex.Message;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        //public List<sysTMenu> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        //{
        //    throw new NotImplementedException();
        //}

        //public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        //{
        //    throw new NotImplementedException();
        //}

    }

    

    public partial class sysTUserRepo
    {

        public bool UpdateIsLock(sysTUser domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE sys_t_user SET IsLock = @islock, Modified_By = @modifiedBy, Modified_Date = @modifiedDate where (lower(user_name) = lower(@userName))";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@islock", SqlDbType.Bit).Value = (object)domain.islock ?? DBNull.Value;
                    cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = (object)domain.userName ?? DBNull.Value;
                    cmd.Parameters.Add("@modifiedBy", SqlDbType.VarChar).Value = (object)domain.modifiedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@modifiedDate", SqlDbType.DateTime).Value = (object)domain.modifiedDate ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        errorLog = ex.Message;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool UpdateLastLogin(sysTUser domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE sys_t_user SET Last_Login = @lastLogin, Modified_By = @modifiedBy, Modified_Date = @modifiedDate where User_Id=@userId";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@lastLogin", SqlDbType.DateTime).Value = (object)domain.lastLogin ?? DBNull.Value;
                    cmd.Parameters.Add("@userId", SqlDbType.Int).Value = domain.userId;
                    cmd.Parameters.Add("@modifiedBy", SqlDbType.VarChar).Value = (object)domain.modifiedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@modifiedDate", SqlDbType.DateTime).Value = (object)domain.modifiedDate ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        errorLog = ex.Message;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool Update(int userId, string password, string auditUser)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE sys_t_user SET Modified_Date = GetDate(), Modified_By = @auditUser, Password = @password,Last_Login=null WHERE User_ID = @userId";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
                    cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
                    cmd.Parameters.Add("@auditUser", SqlDbType.VarChar).Value = auditUser;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        errorLog = ex.Message;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool ValidateUser(string username, string password, out sysTUser t, out string errMsg)
        {
            bool result = false;
            t = null;
            errMsg = string.Empty;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select User_ID, User_Name, Email, Password, IsActive, Mobile, Created_By, Created_Date, Modified_By, Modified_Date FROM sys_t_user where (lower(user_name) = lower(@userName)) and (password = @password)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = username;
                    cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;

                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if (dr.HasRows)
                            {
                                result = true;
                                t = new sysTUser();
                                if (dr.Read())
                                {
                                    t.userId = dr[sysTUser.Fields.userId] == DBNull.Value ? 0 : int.Parse(dr[sysTUser.Fields.userId].ToString());
                                    t.userName = dr[sysTUser.Fields.userName] == DBNull.Value ? string.Empty : dr[sysTUser.Fields.userName].ToString();
                                    t.email = dr[sysTUser.Fields.email] == DBNull.Value ? string.Empty : dr[sysTUser.Fields.email].ToString();
                                    t.password = dr[sysTUser.Fields.password] == DBNull.Value ? string.Empty : dr[sysTUser.Fields.password].ToString();
                                    t.isactive = dr[sysTUser.Fields.isactive] == DBNull.Value ? (bool?)null : bool.Parse(dr[sysTUser.Fields.isactive].ToString());
                                    t.mobile = dr[sysTUser.Fields.mobile] == DBNull.Value ? string.Empty : dr[sysTUser.Fields.mobile].ToString();
                                    t.createdBy = dr[sysTUser.Fields.createdBy] == DBNull.Value ? string.Empty : dr[sysTUser.Fields.createdBy].ToString();
                                    t.createdDate = dr[sysTUser.Fields.createdDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[sysTUser.Fields.createdDate].ToString());
                                    t.modifiedBy = dr[sysTUser.Fields.modifiedBy] == DBNull.Value ? string.Empty : dr[sysTUser.Fields.modifiedBy].ToString();
                                    t.modifiedDate = dr[sysTUser.Fields.modifiedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[sysTUser.Fields.modifiedDate].ToString());

                                                                   }
                            }
                            else
                                result = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        errorLog = ex.Message;
                        errMsg = errorLog;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }    
    }

    public partial class sysTMenuRightsRepo
    {
        public bool Save(List<sysTMenuRights> domains)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmdDelete = conn.CreateCommand();
                    cmdDelete.CommandText = " delete from sys_t_menu_rights where Role_Id = @roleId ";
                    cmdDelete.CommandType = CommandType.Text;

                    cmdDelete.Parameters.Add("@roleId", SqlDbType.Int).Value = domains[0].roleId;

                    cmdDelete.Transaction = txn;
                    cmdDelete.ExecuteNonQuery(); // Delete Data
                    cmdDelete.Dispose();

                    domains.ForEach(delegate(sysTMenuRights domain)
                    {
                        SqlCommand cmdSave = conn.CreateCommand();
                        cmdSave.CommandText = "INSERT INTO sys_t_menu_rights(Menu_ID, Role_ID, Access_Code, Created_Date, Created_By) VALUES(@menuId, @roleId, @accessCode, @createdDate, @createdBy)";

                        cmdSave.Parameters.Add("@menuId", SqlDbType.Int).Value = domain.menuId;
                        cmdSave.Parameters.Add("@roleId", SqlDbType.Int).Value = domain.roleId;
                        cmdSave.Parameters.Add("@accessCode", SqlDbType.VarChar).Value = (object)domain.accessCode ?? DBNull.Value;
                        cmdSave.Parameters.Add("@createdDate", SqlDbType.DateTime).Value = (object)domain.createdDate ?? DBNull.Value;
                        cmdSave.Parameters.Add("@createdBy", SqlDbType.VarChar).Value = (object)domain.createdBy ?? DBNull.Value;

                        cmdSave.Transaction = txn;
                        cmdSave.ExecuteNonQuery(); // Save Data
                        cmdSave.Dispose();
                    });

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    errorLog = ex.Message;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

    }


    public partial class sysAutoComplate
    {

        public string label
        {
            get;
            set;
        }

        public string value
        {
            get;
            set;
        }
    }

}
